package WorldCup2010.DAO;

import java.sql.*;
import java.util.ArrayList;

import WorldCup2010.VO.*;

public class Team {
	private ResultSet rs;
	
	public InfoTeam getTeam(){
		InfoTeam mInfoTeam = new InfoTeam();
		ConnectionFactory connectionFactory = ConnectionFactory.getDefaultFactory();
		connectionFactory.createConnection();
	
		String sql = "select team_no , nation_name, fifaranking, uniform_home, uniform_away, continent, groupno from TEAM";
		rs = connectionFactory.executeQuery(sql);
		
		try {
			rs.next();
			mInfoTeam.setTeam_no(rs.getInt("team_no"));
			mInfoTeam.setNation_name(rs.getString("nation_name"));
			mInfoTeam.setFifaranking(rs.getInt("fifaranking")); 
			mInfoTeam.setUniform_home(rs.getString("uniform_home"));
			mInfoTeam.setUniform_away(rs.getString("uniform_away"));
			mInfoTeam.setContinent(rs.getString("continent"));
			mInfoTeam.setGroupno(rs.getString("groupno"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return mInfoTeam;
	}
	
	public ArrayList<InfoTeam> getAllTeams(String groupNo)
	{
		ArrayList<InfoTeam> list = new ArrayList<InfoTeam>();
		
		ConnectionFactory connectionFactory = ConnectionFactory.getDefaultFactory();
		connectionFactory.createConnection();
		
		String sql = "select team_no , nation_name, fifaranking, uniform_home, uniform_away, continent, groupno from TEAM where groupno = " + "'" +groupNo+"'";		
			
		rs = connectionFactory.executeQuery(sql);
			
		try {
			while(rs.next())
			{
				InfoTeam mInfoTeam = new InfoTeam();
				mInfoTeam.setTeam_no(rs.getInt("team_no"));
				mInfoTeam.setNation_name(rs.getString("nation_name"));
				mInfoTeam.setFifaranking(rs.getInt("fifaranking")); 
				mInfoTeam.setUniform_home(rs.getString("uniform_home"));
				mInfoTeam.setUniform_away(rs.getString("uniform_away"));
				mInfoTeam.setContinent(rs.getString("continent"));
				mInfoTeam.setGroupno(rs.getString("groupno")); 
				
				list.add(mInfoTeam);	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		connectionFactory.closeConnection();
		return list;
	}
	
	public ArrayList<InfoTeam> getPoint()
	{
		ArrayList<InfoTeam> list = new ArrayList<InfoTeam>();
		
		ConnectionFactory connectionFactory = ConnectionFactory.getDefaultFactory();
		connectionFactory.createConnection();
		
//		String sql = "GET_POINT("+ "'"+groupNo +"'"+") as getpoint from Team_MATCH_RESULT";	
		String sql = "select get_point(201051) getpoint, get_win(201051) getwin, get_lose(201051) getlose, get_draw(201051) getdraw from dual";
		rs = connectionFactory.executeQuery(sql);
			
		try {
			while(rs.next())
			{
				InfoTeam mInfoTeam = new InfoTeam();
				mInfoTeam.setGetpoint(rs.getInt("getPoint"));
				mInfoTeam.setGetwin(rs.getInt("getwin"));
				mInfoTeam.setGetlose(rs.getInt("getlose"));
				mInfoTeam.setGetdraw(rs.getInt("getdraw"));
				
				list.add(mInfoTeam);	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		connectionFactory.closeConnection();
		return list;
	}
}

